from __future__ import print_function, unicode_literals
import frappe
from frappe.query_builder.functions import Count,Sum, Cast
from frappe.query_builder import Case, Criterion,Order    #from pypika import Query, Field, Table, Criterion
from frappe.utils import flt, cstr, nowdate, nowtime,rounded, get_user_date_format, add_days, today, now


def get_item_plan_master_map(company):
    item = frappe.qb.DocType('Item')
    item_default = frappe.qb.DocType('Item Default')
    supply_method = Case().when(
                (item_default.supply_method.isnull()), item.default_material_request_type
            ).else_(
                item_default.supply_method
            ).as_('supply_method')
    safety_stock = Case().when(
                (item_default.safety_stock.isnull()), item.safety_stock
            ).else_(
                item_default.safety_stock
            ).as_('safety_stock') 
    min_order_qty = Case().when(
                (item_default.min_order_qty.isnull()), item.min_order_qty
            ).else_(
                item_default.min_order_qty
            ).as_('min_order_qty')
    lead_time_days = Case().when(
                (item_default.lead_time_days.isnull()), item.lead_time_days
            ).else_(
                item_default.lead_time_days
            ).as_('lead_time_days')                  
    data = frappe.qb.from_(
            item
        ).join(
            item_default
        ).on(
            item.name == item_default.parent
        ).select(
            item.name.as_('item_code'),
            item.item_name,
            item.stock_uom,
            item_default.default_supplier,
            item_default.llc,
            item_default.ignore_existing_stock,
            supply_method,
            safety_stock,
            min_order_qty,
            lead_time_days,
            item_default.max_order_qty,
            item_default.qty_multiple,
            item_default.inspection_time,
            item_default.consolidate_demand_days
        ).where(
            item_default.company == company
        ).run(as_dict = True)    
    return {d.item_code:d for d in data}

def get_bom_map(company):
    fields=['item as name', 'quantity','`tabBOM Item`.item_code','`tabBOM Item`.item_name',
            '`tabBOM Item`.stock_qty','`tabBOM Item`.stock_uom','name as bom_no']
    filters={'docstatus':1,'is_default':1, 'company': company}
    data_list = frappe.get_all('BOM', filters= filters, fields = fields)
    return convert_to_dict(data_list)

def convert_to_dict(data_list):
    """convert list of list [[key1,value11,value12],[key2,value21,value22]] from db.sql or get_list
	 to dict {key1:[value11,value12], key2:[value21,value22]}  to improve lookup performance
    """
    data = dict.fromkeys({d[0] if type(d) in (list,tuple) else d.name for d in data_list})
    for d in data_list:
        key, value = (d[0], d[1:]) if type(d) in (list,tuple) else (d.name, d)
        if data[key] and value:
            data[key].append(value)
        else:
            data[key] = [value]
    return data
    
def get_item_code_onhand_map(company):
    # sql = """select item_code, stock_uom,sum(actual_qty) from tabBin bin inner join tabWarehouse wh on bin.warehouse = wh.name 
    #     where wh.company = %s group by item_code, stock_uom"""
    # data = frappe.db.sql(sql,(company,), as_dict=1)
    Bin = frappe.qb.DocType('Bin')
    Warehouse = frappe.qb.DocType('Warehouse')
    data = frappe.qb.from_(
                Bin
            ).join(
                Warehouse
            ).on(
                Bin.warehouse == Warehouse.name
            ).select(
                Bin.item_code,Bin.stock_uom, Sum(Bin.actual_qty).as_("onhand")
            ).groupby(
                Bin.item_code, Bin.stock_uom
            ).where(
                Warehouse.company==company
            ).run(as_dict = True)
    return {d.item_code:d for d in data}

def get_item_code_onhand(item_code, item_code_onhand_map = {}):
        qty_dict = item_code_onhand_map.get(item_code)
        return qty_dict and (qty_dict.onhand, qty_dict.stock_uom) or (0,'')

def get_open_so(warehouses, item_code_list = [], filters = {}, sort_key = None):
    """需求数量以负数表示，即 订单数量折算为库存数量后 乘以 -1
       todo:还要考虑套件销售场景，即销售明细数量在packed item表，而不是sales order item表
    """
    so = frappe.qb.DocType('Sales Order')
    so_item = frappe.qb.DocType('Sales Order Item')
    item = frappe.qb.DocType('Item')
    query = frappe.qb.from_(
                so
            ).join(
                so_item
            ).on(
                so_item.parent == so.name
            ).join(
                item
            ).on(
                so_item.item_code == item.name
            ).select(
                Cast("Sales Order", "Char").as_('ref_doctype'),
                so.name.as_('ref_docname'),
                so_item.name.as_("ref_doc_item"),
                so_item.item_code,
                so_item.delivery_date.as_('due_date'),
                ((so_item.stock_qty * ((so_item.qty - so_item.delivered_qty) / so_item.qty)) * -1).as_('qty'),
                so_item.stock_uom,
                so_item.uom,
                so_item.conversion_factor,
                so_item.item_name,
                Cast('Customer','Char', alias ='partner_type'),
                so.customer.as_('partner'),
                so_item.warehouse
            ).where(
                Criterion.all([
                                item.is_stock_item == 1,
                                so_item.warehouse.isin(warehouses),
                                so.docstatus == 1,
                                so.status != 'Closed',
                                so_item.qty > so_item.delivered_qty
                ])
            )
    if item_code_list:
        query = query.where(
            so_item.item_code.isin(item_code_list)
        )            
    if sort_key:
        query = query.select(
            Cast(sort_key,"Int").as_('sort_key')
        )        
    if filters.get('sales_order'):
        query = query.where(
            so.name == filters.get('sales_order')
        )
    if filters.get('item_code'):
        query = query.where(
            so_item.item_code == filters.get('item_code')
        )
    if filters.get('from_delivery_date'):
        query = query.where(
            so_item.delivery_date >= filters.get('from_delivery_date')
        )
    if filters.get('to_delivery_date'):
        query = query.where(
            so_item.delivery_date <= filters.get('to_delivery_date')
        )    
    result = query.run(as_dict = True)
    return result

def get_open_wo_gi(warehouses, item_code_list = [], sort_key = None):
    '''需求数量以负数表示，即 订单数量折算为库存数量后 乘以 -1
    get open reserved qty for work order'''

    wo = frappe.qb.DocType('Work Order')
    wo_item = frappe.qb.DocType('Work Order Item')
    item = frappe.qb.DocType('Item')
    qty = Case().when(
                (wo.skip_transfer.isnull() | wo.skip_transfer == 0), (wo_item.required_qty - wo_item.transferred_qty) * -1
            ).else_(
                (wo_item.required_qty - wo_item.consumed_qty) * -1
            ).as_(
                "qty"
            )
    qty_where = Case().when(
                (wo.skip_transfer.isnull() | wo.skip_transfer == 0), wo_item.required_qty > wo_item.transferred_qty
            ).else_(
                wo_item.required_qty > wo_item.consumed_qty
            )      
    query = frappe.qb.from_(
                wo
            ).join(
                wo_item
            ).on(
                wo_item.parent == wo.name
            ).join(
                item
            ).on(
                wo_item.item_code == item.name                
            ).select(
                Cast("Work Order", "CHAR").as_('ref_doctype'),
                wo.name.as_('ref_docname'),
                wo_item.name.as_("ref_doc_item"),
                wo_item.item_code,
                wo.production_item.as_('parent_item_code'),
                Cast(wo.planned_start_date,'Date').as_('due_date'),
                qty,
                item.stock_uom,
                item.item_name,
                wo_item.source_warehouse.as_('warehouse')
            ).where(
                Criterion.all([
                    wo_item.source_warehouse.isin(warehouses),
                    wo.docstatus == 1,
                    wo.status.notin(["Stopped", "Completed"]),
                    qty_where
                ])
            )
    if sort_key:
        query = query.select(
            Cast(sort_key,"Int").as_('sort_key')
        )             
    if item_code_list:
        query = query.where(
            wo_item.item_code.isin(item_code_list)
        )
    result = query.run(as_dict = True)
    return result

def get_open_subcontract_gi(warehouses, item_code_list = [], sort_key = None):
    po = frappe.qb.DocType('Purchase Order')
    po_item_sup = frappe.qb.DocType('Purchase Order Item Supplied')
    item = frappe.qb.DocType('Item')
    query = frappe.qb.from_(
                po
            ).join(
                po_item_sup
            ).on(
                po.name == po_item_sup.parent
            ).join(
                item
            ).on(
                po_item_sup.rm_item_code == item.name
            ).select(
                Cast("Purchase Order","CHAR").as_('ref_doctype'),
                Cast("1","INT").as_('is_subcontracted'),
                po.name.as_('ref_docname'),
                po_item_sup.name.as_("ref_doc_item"),
                po_item_sup.rm_item_code.as_('item_code'),
                po_item_sup.main_item_code.as_('parent_item_code'),
                (((po_item_sup.required_qty - po_item_sup.consumed_qty)*po_item_sup.conversion_factor) * -1).as_('qty'),
                po_item_sup.stock_uom,
                po.schedule_date.as_('due_date'),
                po_item_sup.conversion_factor,
                item.item_name,
                Cast("Supplier",'Char', alias = "partner_type"),
                po.supplier.as_("partner"),
                po_item_sup.reserve_warehouse.as_('warehouse')
            ).where(
                Criterion.all([
                    po_item_sup.reserve_warehouse.isin(warehouses),
                    po.docstatus == 1,
                    po_item_sup.required_qty > po_item_sup.consumed_qty,
                    po.status.notin(["Closed", "Delivered"])
                ])
            )
    if sort_key:
        query = query.select(
            Cast(sort_key,"Int").as_('sort_key')
        )             
    if item_code_list:
        query = query.where(
            po_item_sup.rm_item_code.isin(item_code_list)
        )        
    result = query.run(as_dict = True)
    return result        

def get_open_wo(warehouses, item_code_list = [], sort_key = None):
    wo = frappe.qb.DocType('Work Order')
    due_date = Case().when(
                wo.expected_delivery_date.notnull(), wo.expected_delivery_date
                ).when(
                    wo.planned_end_date.notnull(), wo.planned_end_date
                ).else_(
                    wo.planned_start_date
                )
    query = frappe.qb.from_(
                wo
            ).select(
                Cast("Work Order","Char").as_('ref_doctype'),
                wo.name.as_('ref_docname'),
                wo.production_item.as_('item_code'),
                (wo.qty - wo.produced_qty).as_('qty'),
                wo.stock_uom,
                Cast(due_date,'Date').as_("due_date"),
                wo.item_name,
                wo.fg_warehouse.as_('warehouse')
            ).where(
                Criterion.all([
                    wo.fg_warehouse.isin(warehouses),
                    wo.docstatus == 1,
                    wo.status.notin(["Stopped", "Completed"]),
                    (wo.qty - wo.produced_qty) >0 
                ])
            )
    if sort_key:
        query = query.select(
            Cast(sort_key,"Int").as_('sort_key')
        )             
    if item_code_list:
        query = query.where(
            wo.production_item.isin(item_code_list)
        )        
    result = query.run(as_dict = True)
    return result

def get_open_po(warehouses, item_code_list = [], sort_key = None):
    po = frappe.qb.DocType('Purchase Order')
    po_item = frappe.qb.DocType('Purchase Order Item')
    item = frappe.qb.DocType('Item')
    query = frappe.qb.from_(
                po
            ).join(
                po_item
            ).on(
                po.name == po_item.parent
            ).join(
                item
            ).on(
                po_item.item_code == item.name                
            ).select(
                Cast("Purchase Order","Char").as_('ref_doctype'),
                po.name.as_('ref_docname'),
                po_item.name.as_("ref_doc_item"),
                po_item.item_code,
                ((po_item.qty - po_item.received_qty)*po_item.conversion_factor).as_('qty'),
                po_item.stock_uom,
                po_item.schedule_date.as_('due_date'),
                po_item.conversion_factor,
                po_item.item_name,
                Cast("Supplier",'Char', alias = 'partner_type'),
                po.supplier.as_("partner"),
                po_item.warehouse
            ).where(
                Criterion.all([
                    item.is_stock_item == 1,
                    po_item.warehouse.isin(warehouses),
                    po.docstatus == 1,
                    po_item.qty > po_item.received_qty,
                    po.status.notin(["Closed", "Delivered"]),
                    po_item.delivered_by_supplier == 0
                ])
            )
    if sort_key:
        query = query.select(
            Cast(sort_key,"Int").as_('sort_key')
        )             
    if item_code_list:
        query = query.where(
            po_item.item_code.isin(item_code_list)
        )        
    result = query.run(as_dict = True)
    return result

def get_open_mr(warehouses, item_code_list = [], material_types = [], sort_key = None):
    """可以使用case when来实现一个输出数据库不存在字段的固定值字段，
    Case().when(so.name.notnull(),"Sales Order").as_('Ref DocType'),
    Cast("Sales Order","Char").as_('Ref DocType'),
    Cast("Sales Order","Char",alias='Ref DocType'),
    也可以通过列表解析在每个字典元素中增加一个固定键值对
    result = [dict(r, **{'ref_doctype':'Purchase Order'}) for r in result]
    """
    if not material_types:
        material_types = ['Purchase', 'Manufacture', 'Customer Provided', 'Material Transfer']
    mr = frappe.qb.DocType('Material Request')
    mr_item = frappe.qb.DocType('Material Request Item')
    query = frappe.qb.from_(
                mr
            ).join(
                mr_item
            ).on(
                mr.name == mr_item.parent
            ).select(
                Cast("Material Request","Char").as_('ref_doctype'),
                mr.name.as_('ref_docname'),
                mr_item.name.as_("ref_doc_item"),
                mr_item.item_code,
                mr_item.stock_uom,
                mr.schedule_date.as_('due_date'),
                mr_item.conversion_factor,
                mr_item.item_name,
                mr_item.uom,
                mr_item.warehouse
            ).where(
                Criterion.all([
                    mr_item.warehouse.isin(warehouses),
                    mr.docstatus == 1,
                    mr_item.stock_qty > mr_item.ordered_qty,
                    mr.material_request_type.isin(material_types),
                    mr.status.notin(['Stopped'])
                ])
            )
    if material_types[0] == 'Material Issue':
        query = query.select(((mr_item.stock_qty - mr_item.ordered_qty) * -1).as_('qty'))
    else:
        query = query.select((mr_item.stock_qty - mr_item.ordered_qty).as_('qty'))       
    if sort_key:
        query = query.select(
            Cast(sort_key,"Int").as_('sort_key')
        )             
    if item_code_list:
        query = query.where(
            mr_item.item_code.isin(item_code_list)
        ) 
    result = query.run(as_dict = True)
    return result
 
def get_open_mr_gi(warehouses, item_code_list = [], sort_key = None):
    return get_open_mr(warehouses,item_code_list = item_code_list,
        material_types=['Material Issue'], sort_key = sort_key)

def get_open_plan_order(company, item_code_list = [], sort_key = None, mrp = None):        
    if not mrp:                    
        mrp = get_last_mrp(company)            
        
    po = frappe.qb.DocType('Plan Order')
    query = frappe.qb.from_(
                po
            ).select(
                Cast("Plan Order","Char").as_('ref_doctype'),
                po.name.as_('ref_docname'),
                po.item_code,
                po.item_name,
                po.qty,
                po.stock_uom,
                po.due_date,
                Cast(sort_key,"Int").as_('sort_key')
            ).where(
                Criterion.all([
                    po.company == company,
                    po.mrp == mrp
                ])
            )
    if item_code_list:
        query = query.where(
            po.item_code.isin(item_code_list)
        )        
    result = query.run(as_dict = True)
    return result

def get_open_plan_order_items(company, item_code_list = [], sort_key = None, mrp = None):        
    if not mrp:                    
        mrp = get_last_mrp(company)            
        
    po = frappe.qb.DocType('Plan Order')
    po_item = frappe.qb.DocType('Plan Order Item')
    query = frappe.qb.from_(
                po
            ).join(
                po_item
            ).on(
                po_item.parent == po.name
            ).select(
                Cast("Plan Order","Char").as_('ref_doctype'),
                po.name.as_('ref_docname'),
                po_item.name.as_("ref_doc_item"),
                po_item.item_code,
                po_item.item_name,
                po_item.qty,
                po_item.stock_uom,
                po_item.due_date,
                Cast(sort_key,"Int").as_('sort_key')
            ).where(
                Criterion.all([
                    po.company == company,
                    po.mrp == mrp
                ])
            )
    if item_code_list:
        query = query.where(
            po_item.item_code.isin(item_code_list)
        )        
    result = query.run(as_dict = True)        
    return result

def get_last_mrp(company):
    multi_level_mrp = frappe.qb.DocType('Multi Level MRP')
    last_mrp = frappe.qb.from_(
                multi_level_mrp
            ).select(
                multi_level_mrp.name
            ).where(
                Criterion.all([
                    multi_level_mrp.item_code.isnull(),
                    multi_level_mrp.sales_order.isnull(),
                    multi_level_mrp.from_delivery_date.isnull(),
                    multi_level_mrp.to_delivery_date.isnull(),
                    multi_level_mrp.company == company
                ])
            ).orderby(
            multi_level_mrp.modified, order = Order.desc     
            ).limit(
                1
            ).run(as_dict = True)
    return last_mrp and last_mrp[0].name or ''

def get_bom_sub_items(item_code, company, sub_item_list = []):
    """用于获取BOM全部下层物料"""
    filters = {'company': company, 'docstatus': 1, 'is_default': 1, 'item': item_code}
    children = frappe.get_all('BOM', fields=['`tabBOM Item`.item_code'], filters = filters, as_list = True)
    for (child,) in children:
        if not child in sub_item_list:
            sub_item_list.append(child)
        get_bom_sub_items(child, company, sub_item_list)
